/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cassandrawindow;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.sql.Time;
import java.util.*;
import java.util.ArrayList;
import org.apache.avro.generic.GenericData.Array;

/* dau tien nen tao mot column family theo kieu UTF8
 *  create column family author with comparator = UTF8Type
 *  and default_validation_class = UTF8Type
 *  and key_validation_class = UTF8Type;
 */

public class CqlDataManager {
    private Connection con = null;
    private String BANNERCOUNT                  =   "ox_banners";
    private String CAMPAINGNCOUNTCF             =   "CampaignCountCf";
    private String CAMPAIGNVIEWCOUNTCF          =   "CampaignViewCountCf";
    private String BANNERCLICKCOUNTCF           =   "BannerClickCountCf";
    private String BANNERVIEWCOUNTCF            =   "BannerViewCountCf";
    private String LinkDateViewCountCf          =   "LinkDateViewCountCf";

    public CqlDataManager(){            
            try {
                Class.forName("org.apache.cassandra.cql.jdbc.CassandraDriver");
                con = DriverManager.getConnection("jdbc:cassandra://192.168.23.114:9160/AdsData");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

    public List getAllUrl()
    {
        List<String> list = new ArrayList<String>();
        String query = "SELECT key FROM "+ LinkDateViewCountCf;
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                list.add(result.getString("key"));
            }
        } catch (Exception ex){
             System.out.println(ex.getMessage());
        }
        return list;
    }

    public boolean checkExistUrl(String url)
    {
        boolean retValue = false;
        String query = "SELECT key FROM "+ LinkDateViewCountCf +" where key = '"+url+"'";
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                retValue = true;
            }
        } catch (Exception ex){
             System.out.println(ex.getMessage());
        }
        return retValue;
    }

    public List getAllBannerID()
    {
        List<String> list = new ArrayList<String>();
        String query = "SELECT key FROM "+ BANNERCLICKCOUNTCF;        
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                list.add(result.getString("key"));
            }
        } catch (Exception ex){
             System.out.println(ex.getMessage());
        }        
        return list;
    }
    public List getAllCampainID()
    {
        List<String> list = new ArrayList<String>();
        String query = "SELECT key FROM "+ CAMPAINGNCOUNTCF;        

        try {
            Statement stmt = con.createStatement();
            
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                list.add(result.getString("key"));
            }
        } catch (Exception ex){
             System.out.println(ex.getMessage());
        }

        return list;
        
    }
    public void getAllKeyByDate(){
        long count = 0;
        String query = "SELECT * FROM "+CAMPAIGNVIEWCOUNTCF+" LIMIT 1000 ";
         try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                count = count + Integer.valueOf(result.getString("id04_131998"));
                
                //System.out.println(result.getString("id01_Windows"));
                //System.out.println(result.getString("KEY"));
            }
        } catch (Exception ex){
             //System.out.println(ex.getMessage());
        }      

    }
    
    public long getEveryObject(String time){
        long count = 0;        
        String query = "SELECT id04_131998 FROM BannerCount WHERE KEY in " + "(" + time + ")";
        Debug.println(query);
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                count = count + Integer.valueOf(result.getString("id04_131998"));
                //System.out.println(result.getString("id01_Windows"));
                //System.out.println(result.getString("KEY"));
            }
        } catch (Exception ex){
             //System.out.println(ex.getMessage());
        }        
        return count;
    }

    public long getCountClickByBannerIDandTime(String time,String BannerID)
    {
        long count = 0;        
        String query = "SELECT id04_"+BannerID+" FROM BannerCount WHERE KEY in " + "(" + time + ")";        
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                count = count + Integer.valueOf(result.getString("id04_"+BannerID));
            }
        } catch (Exception ex){
             //System.out.println(ex.getMessage());
        }
        return count;       
    }
    
    public long getCountClick(String colum,String intervalTime)
    {
        long count = 0;
        int time=0;
        String query = "SELECT id01_Windows FROM BannerCount WHERE KEY in " + "(" + intervalTime + ")";
        //Debug.println(query);
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                Debug.println(result.getString("id01_Windows"));

                if(result.getString("id01_Windows") == null ? "15479" == null : result.getString("id01_Windows").equals("15479"))
                {
                    Debug.println("ngon");
                }
                if(result.getString("id01_Windows") != null)
                {
                   count = count + Integer.valueOf(result.getString("id01_Windows"));
                   time ++;
                } else{
                    Debug.println("null");
                    continue;                    
                }
                //System.out.println(result.getString("id01_Windows"));
                //System.out.println(result.getString("KEY"));
            }
        } catch (Exception ex){
             System.out.println(ex.getMessage());
        }
        System.out.println(time);
        return count;
    }
    
    public void test(){ 
        String query = "SELECT KEY, 'column_1', 'column_2' FROM author";
        try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                System.out.println(result.getString("KEY"));
                System.out.println(result.getString("column_1"));
                System.out.println(result.getString("column_2"));
            }
        } catch (Exception ex){
             System.out.println(ex.getMessage());  
        }
    }

    public void insert(){
       String query =  "INSERT INTO AdsData.quyen (tweet_id, author, body) VALUES (1742, gwashington, 'I chopped down the cherry tree')";
       try {
            Statement stmt = con.createStatement();
            ResultSet result = stmt.executeQuery(query);   
        } catch (Exception ex){
             System.out.println(ex.getMessage());  
        }
    }



}
